package com.fone.player.storage;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build.VERSION;

import com.fone.player.entity.OfflineCache;
import com.fone.player.util.L;

/**
 * 
 * 缓存文件表适配器
 * 
 * @author wangyang
 * @since 2014年4月29日
 */
public class OfflineCacheFileDataBaseAdapter implements IFoneDatabase {
	private static final String TAG = OfflineCacheFileDataBaseAdapter.class
			.getSimpleName();
	private static final OfflineCacheFileDataBaseAdapter mInstance = new OfflineCacheFileDataBaseAdapter();
	private OperateDataBaseTemplate mGeneralDataBaseTemplate = OperateDataBaseTemplate
			.getInstance();

	private OfflineCacheFileDataBaseAdapter() {

	}

	public static OfflineCacheFileDataBaseAdapter getInstance() {
		return mInstance;
	}

	public int addOfflineCacheFileList(SQLiteDatabase sqliteDatabase,
			List<OfflineCache> offlineCacheList) {
		L.v(TAG, "addOfflineCacheFileList", "start");
		int count = 0;
		try {

			sqliteDatabase.beginTransaction();
			for (OfflineCache offlineCache : offlineCacheList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(OFFLINE_CACHE_FILE_CID,
						offlineCache.getCacheCID());
				contentValues.put(OFFLINE_CACHE_FILE_CCID,
						offlineCache.getCacheCCID());
				contentValues.put(OFFLINE_CACHE_FILE_VIDEO_ID,
						offlineCache.getCacheVideoId());
				contentValues.put(OFFLINE_CACHE_FILE_DEFINITION_TYPE,
						offlineCache.getCacheDefinitionType());
				contentValues.put(OFFLINE_CACHE_FILE_XYZPLAY_URL,
						offlineCache.getCacheXYZPlayUrl());
				contentValues.put(OFFLINE_CACHE_FILE_IMAGE_URL,
						offlineCache.getCacheImageUrl());
				contentValues.put(OFFLINE_CACHE_FILE_EPISODE_NUM,
						offlineCache.getCacheEpisodeNum());
				contentValues.put(OFFLINE_CACHE_FILE_NAME,
						offlineCache.getCacheName());
				contentValues.put(OFFLINE_CACHE_FILE_DOWNLOAD_TYPE,
						offlineCache.getCacheDownloadType());
				contentValues.put(OFFLINE_CACHE_FILE_FRAGMENT_COUNT,
						offlineCache.getCacheFragmentCount());
				contentValues.put(OFFLINE_CACHE_FILE_DOWNLOAD_STATE,
						offlineCache.getCacheDownloadState());
				contentValues.put(OFFLINE_CACHE_FILE_ALREADY_SIZE,
						offlineCache.getCacheAlreadySize());
				contentValues.put(OFFLINE_CACHE_FILE_TOTAL_SIZE,
						offlineCache.getCacheTotalSize());
				contentValues.put(OFFLINE_CACHE_FILE_DURATION,
						offlineCache.getCacheDuration());
				contentValues.put(OFFLINE_CACHE_FILE_STORAGE_PATH,
						offlineCache.getCacheStoragePath());
				contentValues.put(OFFLINE_CACHE_FILE_CONTENT_TYPE,
						offlineCache.getCacheContentType());
				contentValues.put(OFFLINE_CACHE_FILE_SORT_INDEX,
						offlineCache.getSortIndex());
				contentValues.put(OFFLINE_CACHE_FILE_CREATE_TIME,
						System.currentTimeMillis());

				// 重复的文件夹做忽略处理
				if (VERSION.SDK_INT < 8) {
					try {
						sqliteDatabase.insertOrThrow(TB_OFFLINE_CACHE_FILE,
								null, contentValues);
					} catch (Exception e) {
					}

				} else {

					sqliteDatabase
							.insertWithOnConflict(TB_OFFLINE_CACHE_FILE, null,
									contentValues,
									SQLiteDatabase.CONFLICT_IGNORE);
				}
			}
			count = 1;
		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
		} finally {
			sqliteDatabase.setTransactionSuccessful();
			sqliteDatabase.endTransaction();
		}
		return count;
	}

	public int addOfflineCacheFileList(List<OfflineCache> offlineCacheList) {
		L.v(TAG, "addOfflineCacheFileList", "start");
		int count = 0;
		try {

			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.beginTransaction();
			for (OfflineCache offlineCache : offlineCacheList) {
				ContentValues contentValues = new ContentValues();
				contentValues.put(OFFLINE_CACHE_FILE_CID,
						offlineCache.getCacheCID());
				contentValues.put(OFFLINE_CACHE_FILE_CCID,
						offlineCache.getCacheCCID());
				contentValues.put(OFFLINE_CACHE_FILE_VIDEO_ID,
						offlineCache.getCacheVideoId());
				contentValues.put(OFFLINE_CACHE_FILE_DEFINITION_TYPE,
						offlineCache.getCacheDefinitionType());
				contentValues.put(OFFLINE_CACHE_FILE_XYZPLAY_URL,
						offlineCache.getCacheXYZPlayUrl());
				contentValues.put(OFFLINE_CACHE_FILE_IMAGE_URL,
						offlineCache.getCacheImageUrl());
				contentValues.put(OFFLINE_CACHE_FILE_EPISODE_NUM,
						offlineCache.getCacheEpisodeNum());
				contentValues.put(OFFLINE_CACHE_FILE_NAME,
						offlineCache.getCacheName());
				contentValues.put(OFFLINE_CACHE_FILE_DOWNLOAD_TYPE,
						offlineCache.getCacheDownloadType());
				contentValues.put(OFFLINE_CACHE_FILE_FRAGMENT_COUNT,
						offlineCache.getCacheFragmentCount());
				contentValues.put(OFFLINE_CACHE_FILE_DOWNLOAD_STATE,
						offlineCache.getCacheDownloadState());
				contentValues.put(OFFLINE_CACHE_FILE_ALREADY_SIZE,
						offlineCache.getCacheAlreadySize());
				contentValues.put(OFFLINE_CACHE_FILE_TOTAL_SIZE,
						offlineCache.getCacheTotalSize());
				contentValues.put(OFFLINE_CACHE_FILE_DURATION,
						offlineCache.getCacheDuration());
				contentValues.put(OFFLINE_CACHE_FILE_STORAGE_PATH,
						offlineCache.getCacheStoragePath());
				contentValues.put(OFFLINE_CACHE_FILE_CONTENT_TYPE,
						offlineCache.getCacheContentType());
				contentValues.put(OFFLINE_CACHE_FILE_SORT_INDEX,
						offlineCache.getSortIndex());
				contentValues.put(OFFLINE_CACHE_FILE_CREATE_TIME,
						System.currentTimeMillis());

				// 重复的文件夹做忽略处理
				if (VERSION.SDK_INT < 8) {
					try {
						mGeneralDataBaseTemplate.insertOrThrow(
								TB_OFFLINE_CACHE_FILE, null, contentValues);
					} catch (Exception e) {
					}

				} else {

					mGeneralDataBaseTemplate.insertWithOnConflict(
							TB_OFFLINE_CACHE_FILE, null, contentValues,
							SQLiteDatabase.CONFLICT_IGNORE);
				}
			}
			count = 1;
		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
		} finally {
			mGeneralDataBaseTemplate.setTransactionSuccessful();
			mGeneralDataBaseTemplate.endTransaction();
		}
		return count;
	}

	/**
	 * 
	 * 根据缓存CID获取缓存对象
	 * 
	 * @return OfflineCache 缓存对象
	 */
	public OfflineCache getOfflineCacheFileByCID(long CID) {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_OFFLINE_CACHE_FILE);
		sql.append(" where ");
		sql.append(OFFLINE_CACHE_FILE_CID);
		sql.append("=");
		sql.append(CID);
		OfflineCache offlineCache = null;
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			while (cursor.moveToNext()) {
				offlineCache = getOfflineCacheFileByCursor(cursor);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return offlineCache;
	}

	/**
	 * 
	 * 获取缓存文件集合
	 * 
	 * @return ArrayList<OfflineCache> 缓存集合
	 */
	public ArrayList<OfflineCache> getOfflineCacheFileList() {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_OFFLINE_CACHE_FILE);
		ArrayList<OfflineCache> offlineCacheList = null;
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			offlineCacheList = new ArrayList<OfflineCache>();
			while (cursor.moveToNext()) {
				OfflineCache offlineCache = getOfflineCacheFileByCursor(cursor);
				offlineCacheList.add(offlineCache);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return offlineCacheList;
	}

	/**
	 * 
	 * 根据缓存ID获取缓存集合
	 * 
	 * @return ArrayList<OfflineCache> 缓存集合
	 */
	public ArrayList<OfflineCache> getOfflineCacheFileListByCCID(long CCID,
			int downloadState) {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_OFFLINE_CACHE_FILE);
		sql.append(" where ");
		sql.append(OFFLINE_CACHE_FILE_CCID);
		sql.append("=");
		sql.append(CCID);
		switch (downloadState) {
		case OfflineCache.CACHE_STATE_ALL:
			break;
		default:
			sql.append(" and ");
			sql.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
			sql.append("=");
			sql.append(downloadState);
			break;
		}
		ArrayList<OfflineCache> offlineCacheList = null;
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			offlineCacheList = new ArrayList<OfflineCache>();
			while (cursor.moveToNext()) {
				OfflineCache offlineCache = getOfflineCacheFileByCursor(cursor);
				offlineCacheList.add(offlineCache);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return offlineCacheList;
	}

	/**
	 * 
	 * 查询所有出错的cid集合
	 * 
	 * @return ArrayList<Long> 缓存CID集合
	 */
	public ArrayList<Long> getCIDListByError() {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_OFFLINE_CACHE_FILE);
		sql.append(" where ");
		sql.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sql.append("=");
		sql.append(OfflineCache.CACHE_STATE_ERROR);
		ArrayList<Long> CIDList = null;
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}

			// 读取数据
			CIDList = new ArrayList<Long>();
			while (cursor.moveToNext()) {
				OfflineCache offlineCache = getOfflineCacheFileByCursor(cursor);
				CIDList.add(offlineCache.getCacheCID());
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return CIDList;
	}

	/**
	 * 
	 * 根据缓存CID获取缓存对象
	 * 
	 * @param CID
	 *            缓存CID
	 * @return OfflineCache 缓存对象
	 */
	public boolean isOfflineCacheFileByCID(long CID) {
		boolean result = false;
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_OFFLINE_CACHE_FILE);
		sql.append(" where ");
		sql.append(OFFLINE_CACHE_FILE_CID);
		sql.append("=");
		sql.append(CID);
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				result = false;
			}
			if (cursor.getCount() > 0) {
				result = true;
			} else {
				result = false;
			}
		} catch (Exception e) {

		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 
	 * 根据CID判断此对象是否在缓存文件表中
	 * 
	 * @return OfflineCache 缓存对象
	 */
	public OfflineCache getOfflineCacheByCID(OfflineCache offlineCache) {
		StringBuilder sqlFolder = new StringBuilder("select * from ");
		sqlFolder.append(TB_OFFLINE_CACHE_FILE);
		sqlFolder.append(" where ");
		sqlFolder.append(OFFLINE_CACHE_FILE_CID);
		sqlFolder.append("=");
		sqlFolder.append(offlineCache.getCacheCID());
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sqlFolder.toString());

			if (cursor != null && cursor.getCount() > 0) {

				if (cursor.moveToNext()) {
					offlineCache.setCacheAlreadySize(cursor.getLong(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_ALREADY_SIZE)));
					offlineCache.setCacheTotalSize(cursor.getLong(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_TOTAL_SIZE)));
					offlineCache
							.setCacheDownloadState(cursor.getInt(cursor
									.getColumnIndex(OFFLINE_CACHE_FILE_DOWNLOAD_STATE)));
					offlineCache.setCacheIsDownlaod(true);
					offlineCache.setCacheStoragePath(cursor.getString(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_STORAGE_PATH)));
					offlineCache.setSortIndex(cursor.getInt(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_SORT_INDEX)));
				}

			} else {
				offlineCache
						.setCacheDownloadState(OfflineCache.CACHE_STATE_NOT_DOWNLOAD);
				offlineCache.setCacheIsDownlaod(false);
			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}

		return offlineCache;
	}

	/**
	 * 
	 * 根据VideoId判断此对象是否在缓存文件表中
	 * 
	 * @return OfflineCache 缓存对象
	 */
	public OfflineCache getOfflineCacheByVideoId(OfflineCache offlineCache) {
		StringBuilder sqlFolder = new StringBuilder("select * from ");
		sqlFolder.append(TB_OFFLINE_CACHE_FILE);
		sqlFolder.append(" where ");
		sqlFolder.append(OFFLINE_CACHE_FILE_VIDEO_ID);
		sqlFolder.append("=");
		sqlFolder.append(offlineCache.getCacheVideoId());
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sqlFolder.toString());

			if (cursor != null && cursor.getCount() > 0) {

				if (cursor.moveToNext()) {
					offlineCache.setCacheAlreadySize(cursor.getLong(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_ALREADY_SIZE)));
					offlineCache.setCacheTotalSize(cursor.getLong(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_TOTAL_SIZE)));
					offlineCache
							.setCacheDownloadState(cursor.getInt(cursor
									.getColumnIndex(OFFLINE_CACHE_FILE_DOWNLOAD_STATE)));
					offlineCache.setCacheIsDownlaod(true);
					offlineCache.setCacheStoragePath(cursor.getString(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_STORAGE_PATH)));
					offlineCache.setSortIndex(cursor.getInt(cursor
							.getColumnIndex(OFFLINE_CACHE_FILE_SORT_INDEX)));
				}

			} else {
				offlineCache
						.setCacheDownloadState(OfflineCache.CACHE_STATE_NOT_DOWNLOAD);
				offlineCache.setCacheIsDownlaod(false);
			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}

		return offlineCache;
	}

	/**
	 * 
	 * 获取已缓存文件列表的数量
	 * 
	 * @return int 已缓存文件数量
	 */
	public int getOfflineCacheFileFinishCount() {
		int result = 0;
		StringBuilder sqlFolder = new StringBuilder("select * from ");
		sqlFolder.append(TB_OFFLINE_CACHE_FILE);
		sqlFolder.append(" where ");
		sqlFolder.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sqlFolder.append("=");
		sqlFolder.append(OfflineCache.CACHE_STATE_FINISH);
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sqlFolder.toString());

			if (cursor != null) {

				result = cursor.getCount();

			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}

		// 由C层返回(暂时不用,用的时候再解开,调用CacheDownloadManager.getInstance().getCacheFinishCount())
		// result = SharedPreferenceModule.getInstance().getInt(
		// OfflineCache.OFFLINE_CACHE_FINISH_COUNT);
		return result;
	}

	/**
	 * 
	 * 获取指定ccid下已缓存文件列表的数量
	 * 
	 * @return int 已缓存文件数量
	 */
	public int getOfflineCacheFileFinishCountByCCID(long ccid) {
		int result = 0;
		StringBuilder sqlFolder = new StringBuilder("select * from ");
		sqlFolder.append(TB_OFFLINE_CACHE_FILE);
		sqlFolder.append(" where ");
		sqlFolder.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sqlFolder.append("=");
		sqlFolder.append(OfflineCache.CACHE_STATE_FINISH);
		sqlFolder.append(" and ");
		sqlFolder.append(OFFLINE_CACHE_FILE_CCID);
		sqlFolder.append("=");
		sqlFolder.append(ccid);
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sqlFolder.toString());

			if (cursor != null) {

				result = cursor.getCount();

			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}

		// 由C层返回(暂时不用,用的时候再解开,调用CacheDownloadManager.getInstance().getCacheFinishCount())
		// result = SharedPreferenceModule.getInstance().getInt(
		// OfflineCache.OFFLINE_CACHE_FINISH_COUNT);
		return result;
	}

	/**
	 * 
	 * 获取所有非剧集类型下已缓存文件列表
	 * 
	 * @return List<OfflineCache> 已缓存非剧集文件集合
	 */
	public ArrayList<OfflineCache> getOfflineCacheFileListUnDramByStateFinish() {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_OFFLINE_CACHE_FILE);
		sql.append(" where ");
		sql.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sql.append("=");
		sql.append(OfflineCache.CACHE_STATE_FINISH);
		sql.append(" and ");
		sql.append(OFFLINE_CACHE_FILE_CONTENT_TYPE);
		sql.append("!=");
		sql.append(OfflineCache.CACHE_CONTENT_TYPE_ANIMATION);
		sql.append(" and ");
		sql.append(OFFLINE_CACHE_FILE_CONTENT_TYPE);
		sql.append("!=");
		sql.append(OfflineCache.CACHE_CONTENT_TYPE_TV);
		sql.append(" and ");
		sql.append(OFFLINE_CACHE_FILE_CONTENT_TYPE);
		sql.append("!=");
		sql.append(OfflineCache.CACHE_CONTENT_TYPE_VARIETY);
		sql.append(" order by ");
		sql.append(OFFLINE_CACHE_FILE_CREATE_TIME);
		sql.append(" desc");
		Cursor cursor = null;
		ArrayList<OfflineCache> offlineCacheList = new ArrayList<OfflineCache>();
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}
			// 读取数据
			while (cursor.moveToNext()) {
				OfflineCache offlineCache = getOfflineCacheFileByCursor(cursor);
				offlineCacheList.add(offlineCache);
			}
		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}

		return offlineCacheList;
	}

	/**
	 * 
	 * 获取指定ccid下所有文件列表的数量
	 * 
	 * @return int 所有文件数量
	 */
	public int getOfflineCacheFileAllCountByCCID(long ccid) {
		int result = 0;
		StringBuilder sqlFolder = new StringBuilder("select * from ");
		sqlFolder.append(TB_OFFLINE_CACHE_FILE);
		sqlFolder.append(" where ");
		sqlFolder.append(OFFLINE_CACHE_FILE_CCID);
		sqlFolder.append("=");
		sqlFolder.append(ccid);
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sqlFolder.toString());

			if (cursor != null) {

				result = cursor.getCount();

			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 
	 * 获取未缓存完成文件列表的数量
	 * 
	 * @return int 未缓存文件数量
	 */
	public int getOfflineCacheFileNotFinishCount() {
		int result = 0;
		StringBuilder sqlFolder = new StringBuilder("select * from ");
		sqlFolder.append(TB_OFFLINE_CACHE_FILE);
		sqlFolder.append(" where ");
		sqlFolder.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sqlFolder.append("!=");
		sqlFolder.append(OfflineCache.CACHE_STATE_FINISH);
		Cursor cursor = null;
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sqlFolder.toString());

			if (cursor != null) {

				result = cursor.getCount();

			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 
	 * 获取未缓存完成文件列表
	 * 
	 * @return int 未缓存文件列表
	 */
	public List<OfflineCache> getOfflineCacheFileNotFinishList() {
		StringBuilder sqlFolder = new StringBuilder("select * from ");
		sqlFolder.append(TB_OFFLINE_CACHE_FILE);
		sqlFolder.append(" where ");
		sqlFolder.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sqlFolder.append("!=");
		sqlFolder.append(OfflineCache.CACHE_STATE_FINISH);
		sqlFolder.append(" and ");
		sqlFolder.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sqlFolder.append("!=");
		sqlFolder.append(OfflineCache.CACHE_STATE_PAUSE);
		sqlFolder.append(" and ");
		sqlFolder.append(OFFLINE_CACHE_FILE_DOWNLOAD_STATE);
		sqlFolder.append("!=");
		sqlFolder.append(OfflineCache.CACHE_STATE_ERROR);
		Cursor cursor = null;
		List<OfflineCache> offlineCacheList = new ArrayList<OfflineCache>();
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sqlFolder.toString());

			if (cursor == null) {
				return offlineCacheList;
			}
			// 读取数据
			while (cursor.moveToNext()) {
				OfflineCache offlineCache = getOfflineCacheFileByCursor(cursor);
				offlineCacheList.add(offlineCache);
			}

		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return offlineCacheList;
	}

	/**
	 * 
	 * 根据缓存文件Id列表删除文件对象 <br>
	 * 同步方法
	 * 
	 * @param offlineCacheFileList
	 *            缓存列表
	 * @return int 操作结果<br>
	 *         1 删除成功<br>
	 *         -1 删除异常<br>
	 */
	public int deleteOfflineCacheFileByCID(
			List<OfflineCache> offlineCacheFileList) {
		int result = 1;
		try {

			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.beginTransaction();
			for (OfflineCache offlineCacheFile : offlineCacheFileList) {
				StringBuilder where = new StringBuilder();
				where.append(OFFLINE_CACHE_FILE_CID).append("=?");

				mGeneralDataBaseTemplate.delete(TB_OFFLINE_CACHE_FILE, where
						.toString(), new String[] { String
						.valueOf(offlineCacheFile.getCacheCID()) });
			}
			mGeneralDataBaseTemplate.setTransactionSuccessful();
		} catch (Exception e) {
			result = -1;
		} finally {

			mGeneralDataBaseTemplate.endTransaction();
			mGeneralDataBaseTemplate.close();
		}
		return result;
	}

	/**
	 * 
	 * 根据缓存where条件获取缓存对象集合
	 * 
	 * @return List<OfflineCache> 缓存对象集合
	 */
	public List<OfflineCache> getOfflineCacheFileByWhere(String where) {
		StringBuilder sql = new StringBuilder("select * from ");
		sql.append(TB_OFFLINE_CACHE_FILE);
		sql.append(" where ");
		sql.append(where);
		OfflineCache offlineCache = null;
		Cursor cursor = null;
		List<OfflineCache> offlineCacheList = new ArrayList<OfflineCache>();
		try {
			mGeneralDataBaseTemplate.open();
			cursor = mGeneralDataBaseTemplate.select(sql.toString());

			if (cursor == null) {
				return null;
			}
			// 读取数据
			while (cursor.moveToNext()) {
				offlineCache = getOfflineCacheFileByCursor(cursor);
				offlineCacheList.add(offlineCache);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			mGeneralDataBaseTemplate.close();
		}
		return offlineCacheList;
	}

	/**
	 * 单个更新文件表
	 * 
	 * @param offlineCache
	 *            缓存文件
	 * @return
	 */
	public int updateOfflineCacheFile(OfflineCache offlineCache) {
		L.v(TAG,
				"updateOfflineCacheFile",
				"start name=" + offlineCache.getCacheName()
						+ offlineCache.getCacheEpisodeNum() + " downloadState="
						+ offlineCache.getCacheDownloadState());
		List<OfflineCache> offlineCacheList = new ArrayList<OfflineCache>();
		offlineCacheList.add(offlineCache);
		return updateOfflineCacheFileList(offlineCacheList);
	}

	/**
	 * 
	 * 批量更新文件表
	 * 
	 * @param offlineCacheList
	 *            缓存文件列表
	 * @return int 0失败,1成功
	 */
	public int updateOfflineCacheFileList(List<OfflineCache> offlineCacheList) {
		int count = 0;
		try {
			mGeneralDataBaseTemplate.open();
			mGeneralDataBaseTemplate.beginTransaction();
			for (OfflineCache offlineCache : offlineCacheList) {
				String where = OFFLINE_CACHE_FILE_CID + "=? and "
						+ OFFLINE_CACHE_FILE_DEFINITION_TYPE + "=?";
				String[] whereValue = {
						String.valueOf(offlineCache.getCacheCID()),
						String.valueOf(offlineCache.getCacheDefinitionType()) };

				ContentValues cv = new ContentValues();
				if (offlineCache.getCacheXYZPlayUrl() != null
						&& !"".equals(offlineCache.getCacheXYZPlayUrl())) {
					cv.put(OFFLINE_CACHE_FILE_XYZPLAY_URL,
							offlineCache.getCacheXYZPlayUrl());
				}
				if (offlineCache.getCacheImageUrl() != null
						&& !"".equals(offlineCache.getCacheImageUrl())) {
					cv.put(OFFLINE_CACHE_FILE_IMAGE_URL,
							offlineCache.getCacheImageUrl());
				}
				if (offlineCache.getCacheName() != null
						&& !"".equals(offlineCache.getCacheName())) {
					cv.put(OFFLINE_CACHE_FILE_NAME, offlineCache.getCacheName());
				}

				cv.put(OFFLINE_CACHE_FILE_DOWNLOAD_TYPE,
						offlineCache.getCacheDownloadType());
				cv.put(OFFLINE_CACHE_FILE_DOWNLOAD_STATE,
						offlineCache.getCacheDownloadState());

				if (offlineCache.getCacheAlreadySize() != 0) {
					cv.put(OFFLINE_CACHE_FILE_ALREADY_SIZE,
							offlineCache.getCacheAlreadySize());
				}
				if (offlineCache.getCacheTotalSize() != 0) {
					cv.put(OFFLINE_CACHE_FILE_TOTAL_SIZE,
							offlineCache.getCacheTotalSize());
				}
				if (offlineCache.getCacheDuration() != 0) {
					cv.put(OFFLINE_CACHE_FILE_DURATION,
							offlineCache.getCacheDuration());
				}
				if (offlineCache.getCacheStoragePath() != null
						&& !"".equals(offlineCache.getCacheStoragePath())) {
					cv.put(OFFLINE_CACHE_FILE_STORAGE_PATH,
							offlineCache.getCacheStoragePath());
				}
				count = mGeneralDataBaseTemplate.update(TB_OFFLINE_CACHE_FILE,
						cv, where, whereValue);
			}
			count = 1;
		} catch (Exception e) {
			if (e != null) {
				e.printStackTrace();
			}
		} finally {
			mGeneralDataBaseTemplate.setTransactionSuccessful();
			mGeneralDataBaseTemplate.endTransaction();
		}
		return count;
	}

	/**
	 * 
	 * 根据游标读取字段信息
	 * 
	 * @param cursor
	 * @return OfflineCache
	 */
	private OfflineCache getOfflineCacheFileByCursor(Cursor cursor) {
		OfflineCache offlineCache = new OfflineCache();
		offlineCache.setCacheCID(cursor.getLong(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_CID)));
		offlineCache.setCacheCCID(cursor.getLong(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_CCID)));
		offlineCache.setCacheVideoId(cursor.getLong(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_VIDEO_ID)));
		offlineCache.setCacheName(cursor.getString(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_NAME)));
		offlineCache.setCacheImageUrl(cursor.getString(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_IMAGE_URL)));
		offlineCache.setCacheEpisodeNum(cursor.getString(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_EPISODE_NUM)));
		offlineCache.setCacheAlreadySize(cursor.getLong(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_ALREADY_SIZE)));
		offlineCache.setCacheTotalSize(cursor.getLong(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_TOTAL_SIZE)));
		offlineCache.setCacheDownloadState(cursor.getInt(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_DOWNLOAD_STATE)));
		offlineCache.setCacheContentType(cursor.getInt(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_CONTENT_TYPE)));
		offlineCache.setCacheDefinitionType(cursor.getInt(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_DEFINITION_TYPE)));
		offlineCache.setCacheDownloadType(cursor.getInt(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_DOWNLOAD_TYPE)));
		offlineCache.setCacheDuration(cursor.getLong(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_DURATION)));
		offlineCache.setCacheFragmentCount(cursor.getInt(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_FRAGMENT_COUNT)));
		offlineCache.setCacheStoragePath(cursor.getString(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_STORAGE_PATH)));
		offlineCache.setCacheXYZPlayUrl(cursor.getString(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_XYZPLAY_URL)));
		offlineCache.setSortIndex(cursor.getInt(cursor
				.getColumnIndex(OFFLINE_CACHE_FILE_SORT_INDEX)));
		offlineCache.setCacheIsDownlaod(true);
		offlineCache.setCacheHierarchyType(OfflineCache.CACHE_HIERARCHY_FILE);
		return offlineCache;
	}
}
